package at.fhv.gis.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbManagerSqlite implements DbManager {
	private final Connection _connection;
	private static final String CONNECT_STRING = "jdbc:sqlite:/gis.db";
	private static final String DBUSER = "ctt";
	private static final String DBPW = "start123";

	private static final String SQL_ALL_POINTS = "select point_id, st_asgeojson(geometry) from point";
	private static final String SQL_ALL_LINES = "select line_id, st_asgeojson(geometry) from line";
	private static final String SQL_ALL_POLYGONS = "select polygon_id, st_asgeojson(geometry) from polygon";
	private static final String SQL_ALL_GEOM_IN_BBOX = "select point.point_id, st_asgeojson(point.geometry) from point where point.geometry && ST_MakeEnvelope(?, ?, ?, ?, 4326) "
			+ "union all select polygon.polygon.id, st_asgeojson(polygon.geometry) from polygon where polygon.geometry && ST_MakeEnvelope(?, ?, ?, ?, 4326) "
			+ "union all select line.line_id, st_asgeojson(line.geometry) from line where line.geometry && ST_MakeEnvelope(? ?, ?, ?, 4326)";

	public DbManagerSqlite() throws ClassNotFoundException, SQLException {
		Class.forName("SQLite.JDBCDriver");
		_connection = DriverManager.getConnection(CONNECT_STRING, DBUSER, DBPW);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.fhv.gis.db.DbManager#getAllPoints()
	 */
	@Override
	public String getAllPoints() throws SQLException {
		Statement st = _connection.createStatement();
		ResultSet result = st.executeQuery(SQL_ALL_POINTS);
		StringBuilder b = new StringBuilder();

		b.append("{\"type\":\"FeatureCollection\",\"features\":[");

		while (result.next()) {
			b.append("{\"type\":\"Feature\",");
			b.append("\"id\":\"" + result.getString(1) + "\"");

			b.append(",\"geometry\":");
			b.append(result.getString(2));
		}

		b.append("}]}");

		return b.toString();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.fhv.gis.db.DbManager#getAllLines()
	 */
	@Override
	public String getAllLines() throws SQLException {
		Statement st = _connection.createStatement();
		ResultSet result = st.executeQuery(SQL_ALL_LINES);
		StringBuilder b = new StringBuilder();

		b.append("{\"type\":\"FeatureCollection\",\"features\":[");

		while (result.next()) {
			b.append("{\"type\":\"Feature\",");
			b.append("\"id\":\"" + result.getString(1) + "\"");

			b.append(",\"geometry\":");
			b.append(result.getString(2));
		}

		b.append("}]}");

		return b.toString();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.fhv.gis.db.DbManager#getAllPolygons()
	 */
	@Override
	public String getAllPolygons() throws SQLException {
		Statement st = _connection.createStatement();
		ResultSet result = st.executeQuery(SQL_ALL_POLYGONS);
		StringBuilder b = new StringBuilder();

		b.append("{\"type\":\"FeatureCollection\",\"features\":[");

		while (result.next()) {
			b.append("{\"type\":\"Feature\",");
			b.append("\"id\":\"" + result.getString(1) + "\"");

			b.append(",\"geometry\":");
			b.append(result.getString(2));
		}

		b.append("}]}");

		return b.toString();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.fhv.gis.db.DbManager#getFromBoundingBox(double, double, double,
	 * double)
	 */
	@Override
	public String getFromBoundingBox(double long1, double lat1, double long2,
			double lat2) throws SQLException {
		PreparedStatement st = _connection
				.prepareStatement(SQL_ALL_GEOM_IN_BBOX);
		st.setDouble(1, long1);
		st.setDouble(2, lat1);
		st.setDouble(3, long2);
		st.setDouble(4, lat2);

		st.setDouble(5, long1);
		st.setDouble(6, lat1);
		st.setDouble(7, long2);
		st.setDouble(8, lat2);

		st.setDouble(9, long1);
		st.setDouble(10, lat1);
		st.setDouble(11, long2);
		st.setDouble(12, lat2);

		ResultSet result = st.executeQuery();

		StringBuilder b = new StringBuilder();

		b.append("{\"type\":\"FeatureCollection\",\"features\":[");

		while (result.next()) {
			b.append("{\"type\":\"Feature\",");
			b.append("\"id\":\"" + result.getString(1) + "\"");

			b.append(",\"geometry\":");
			b.append(result.getString(2));
		}

		b.append("}]}");

		return b.toString();
	}
}
